The XYE menu provides you with three new charts that are otherwise difficult to create in Excel. The first is an XY scatter plot with errorbars. Under standard Excel you may have errorbars for curves with fixed X distances (e.g. stock prices, where the X interval is always 1 day). However, you may not have errorbars for curves with user defined X values. The second new type of chart - the cluster plot - is a series of columns, one following the other rather than side by side. Under standard Excel if you have two or more series of data you may display them side by side as columns on a point by point basis (e.g. series1bar1, series2bar1, series1bar2, series2bar2 ...) while you may not display them side by side as consecutive clusters of points (e.g. series1bar1, series1bar2..., series2bar1, series2bar2, ...). A cluster plot has columns displayed in the second format with errorbars (you may set the error to zero if necessary). The third new type of chart is the standard column chart with errorbars for multiple series (standard Excel limits stock-price style errorbars to just one series of data for column charts).
The file XYEM (pronounced like the second syllable of 'enzyme') contains the code to add XYE Menu to your copy of Excel. XYE Menu is a menu, not a menu bar. XYE Menu (this product, with a capital “M”) places the XYE menu (the menu, with a lower case “m”) in the Full Worksheet and Full Chart menu bars of Excel. XYE Menu is copyrighted by Bang Two Rocks, a software tool company. If there is any software you would like to see created, let us know.
Getting Started
You will need Excel 3.0 or greater to use XYE Menu. To access XYE Menu, simply open the file XYEM which contains the macros that make up the XYE menu. You may place it in a startup folder of macros to run when Excel is launched if you wish (conventionally this is done by placing the document in the folder “Excel Startup Folder” within your System Folder). When you open XYEM, the command “Add/Delete XYE Menu” is added to the bottom of the options menu. You may use this command to turn the menu on and off, conserving the amount of space in the menu bar. If you wish to start with the menu installed and ready to run, check the “Start with menu enabled” option in the “XYE Menu Defaults” dialog box.
The menu in the Full Worksheet menu bar is divided into five sections. The first section contains commands relevant to XY plots with errorbars, simply called error plots. The second section contains commands relevant to creating columns charts of consecutive series of data, simply called cluster plots. The third section contains the commands relevant to standard column charts with errorbars. The fourth section contains commands relevant to calculating the standard error of the mean for a group of data. The fifth section contains commands relevant to all of XYE Menu, such as the on-line help.
XYE Menu Defaults, Expert Mode, and Saving Default Settings
There are two levels of defaults, those that affect a specific type of plot and those that affect every type of plot. Each type of plot has a set of defaults that may be changed by selecting its "defaults" menu item (found in the appropriate section of the XYE menu). There are also global defaults, such as concealed dataspace, that affect the way XYE Menu works. These may be accessed by pressing the button “XYE Menu Defaults” found in any of the dialog boxes used in editing the specific defaults for any type of plot or selecting "Defaults for XYE Menu" from the XYE Menu. Help for any of these default settings may be obtained by pressing the “?” button next to each default setting. At the expense of having a more cluttered menu, you can set XYE Menu to expert mode by checking the “Expert Mode” option in the dialog box for global defaults. This will allow you to easily access interactive plot modes (see below).
All default settings, both global defaults and plot defaults, apply only to the current Excel session. If you wish to save your default settings for future sessions press the “OK and Save Settings” button found in the XYE Menu defaults dialog box.
Worksheet Commands
Building XY Error Plots
To build a plot, you need to perform three steps: 1) Select your X, Y and Error data 2) Select the command “Create XY Error Plot” from the XYE menu and 3) Choose a dataspace for the macro to use as workspace for each curve. A demonstration of this is available using the “Show Me an XY Error Plot” button in the help dialog box (presented by selecting “XYE Menu Help” from the XYE menu). You may optionally perform a fourth step, that of controlling how your data will look, using the “Interactive XY Error Plot” or “Add XY Error Curve” menu items. In this case a dialog box will come up for every curve you plot, asking you what type of error lines and error ticks you would like for the selected data cells. Technically, steps 1 or 3 may also be optional. If you ignore the first step and start the plot without enough data selected, you pop into interactive mode where you may indicate the location of your data cells. If you don’t wish to view the dataspace, Excel will conceal the dataspace for you and eliminate the third step when you select the concealed dataspace option in the XYE Menu Defaults (see the top section for information about defaults, see the section on “How It Works” at the bottom for information about dataspace). For those who don’t mind the computer reserving the first few columns of their worksheet, concealed dataspace is strongly recommended.
If you choose to embed new charts on your worksheet, you will see the plot underneath your X data. If you choose to create new chart windows for your plots, these new windows are pushed to the back so that you may continue with your work. Use the “Window” menu to bring the chart containing your plot to the front. You may change the defaults for new plots in the XYE Menu Defaults. Embedded charts are strongly recommended for those who don’t like losing the data that goes with their charts or the charts that go with their data. You may drag and resize an embedded window as you would any Excel object. Double click the embedded chart to open it as a chart window; it will be changed to an independent chart window if saved while open.
Selecting Data For XY Error Plots
The first step to building an error plot is to select the data. The first three ranges must be a column of X values, a column of Y values, and a column of Error values (E). The data must be in columnar form. That is, the X values are stored in a range of cells stacked one on top of the other, the Y values stacked one on top of the other in another column, etc. You may select the data as a contiguous block of cells (several columns of data next to each other) or as a noncontiguous multiple selection (by holding down the control key while dragging the mouse through additional ranges of data). When using noncontiguous ranges, each range may contain one or more columns of cells. The program breaks up multicolumn ranges into ranges of one column each. Following the first three ranges, the subsequent ranges may again be of the form X, Y, E; known as (XYE)(XYE) entry, or you may just use Y, E; known as (XYE)(YE)(YE) entry. The same X values will be used for all curves in (XYE)(YE)(YE) entry. The error values are used to calculate +/- error values, you may change the high and low error values by editing the dataspace if the error values are not simple dispersion values. If a label is located in the cell directly above a Y column, this label will be used in the legend for the data. If the cell is blank and the “Generate names for unlabeled curves/clusters” option is set a generic label (e.g. “Curve 1”) is created and placed in the cell. You may optionally change the label and column entries in the interactive dialog box.
Selecting the Dataspace
If you are using the concealed dataspace option, you won’t have to worry about this process. After you select any plot command, you will be asked for an upper left hand corner for the “dataspace” for each curve. In order to create the plot, the data that you enter is manipulated into a form that will produce the desired plot. The plot is then built using commands provided by Excel. You select the upper left hand by typing a cell location or clicking on a cell (you may move the dialog by dragging its title if necessary). This cell should be next to a large range of empty cells. If you use the “Chosen & Checked” option in the XYE Menu Defaults, the computer will make sure the necessary number of cells are empty before letting you proceed.
XY Error Dialog Box Items:
If you start the plot without enough data selected or use the interactive mode (see "Expert Mode" of "XYE Menu Defaults"), a dialog box containing the following items will appear.
Direction of Errorbars
Error Ticks
Error Lines
What do the errorbars look like? There are three groups of option buttons that describe the appearance of the errorbar: Direction, Error Ticks, and Error Lines. They are pretty much self explanatory.
Chart
Where will the plot appear? You may create a new chart or add the curve to any open chart. Select the destination by clicking a radio button in the “Chart” box. If there are no charts already open, a message to this effect will appear in the chart box and the option will be disabled. You may control the placement of new charts in independent windows or as imbedded by selecting the “New Chart Window” or “Embedded” options from the dialog presented by the "Defaults for XYE Menu” item.
Multiple Input Style
If you have more than one curve to plot, how shall multiple columns be interpreted? “Multiple Input Style” can be set so you only enter X data once. If you choose “(XYE)(XYE)...” every three columns define a new curve, while “X(YE)(YE)...” uses the first range as X coordinates and defines a new curve with the same X for every two following Y and E ranges. You may change styles in the middle of multiple plots if you are careful and have a need to do so. Selecting “(XYE)(XYE)...” tells the program to use the next three ranges of data as X, Y, and E columns for the next curve. Selecting “X(YE)(YE)...” tells the program to use current X data in the next curve and use the next two ranges of data as Y and E for the next curve. Input style is irrelevant if you have only one curve to plot.
Cells
The range of each column of cells used for the current curve are in the upper left hand of the dialog box. The data columns may be changed directly. Just hit the tab key until the value you wish to replace is selected, move the dialog box by dragging its title, find your data, and then click and drag on the range you wish to select. You may also type the cell reference if you prefer.
Label
There are two styles of labels, linked and unlinked. This preference is selected in the "Linked names ..." checkbox in the XYE Menu Defaults (described at the top of this document). The cleanest plots may be obtained by putting a label for each column of Y values in a cell above the range containing that column of Y values. If you checked "Link names in legend to worksheet” preference in the XYE Menu Defaults, the label above the Y data (or next to cluster data, for cluster plots) will be linked to the legend, if you choose to show the legend. If you change the label in your worksheet, the updated label will be displayed in the legend. If the data starts in the first row, linking is ignored for noninteractive plots: there can be no linking to a cell in the nonexistent row zero.
If you have linked labels the dialog box will read "Name Cell" and require a cell. Click on the appropriate cell or type in a reference. If you have unlinked labels the dialog box will read "Label" and require text. Whatever name you type in the dialog box will appear in the legend. You may still type in the label of a cell, but that will appear as a text name on your plot. Why an unlinked label? Sometimes the name for a label may not be found in the cell above the Y data because the data comes from a larger column of data. The label for the curve (used by the legend) is left unlinked and may be typed into the dialog directly. Note: Excel does not properly display legends for plots consisting of one curve.
Another preference you may set in the XYE Menu Defaults is “Generate names for unlabeled curves/clusters”. If the cell that would contain the label for a curve is blank, a name will be generated and placed in the cell. A name will replace only a blank cell, any kind of data in the cell will suppress name generation. Your data is safe.
Error Only
The “Error Only” check box allows you to control the display of a curve with the error. If you already have a best fit curve and just want to superimpose the symbols and errorbars over it, check this box to stop the curve from appearing with the errorbars.
Silent
The “Silent” check box is perhaps the most useful box. It prevents the dialog box from returning. If you check this box all remaining preselected data will be entered using the selected attributes and appear in the selected chart. If a new chart is selected at the time silent mode is selected, all curves will appear in that new chart. To create many new charts, you must run interactively or select and create them one by one.
The XY Error Defaults Dialog Box
When you create a plot noninteractively, the default settings have to come from somewhere. This is the place. All the items in this dialog box are also available for interactively created plots and are described directly above in the section on the interactive dialog box with the exception of "Plot Type".
There are two ways to create XY error plots. Both have strengths and shortcomings. If you wish to use the type of plot that allows you to have an Excel legend, you give up the ability to interactively change line colors and symbol styles. If you wish to have a plot where you can easily customize the lines and symbols by double clicking them, you lose the ability to use the standard Excel legend. We therefore give you both options, you may choose to have an Excel legend or a customizable plot. There are two options for a custom plot, with or without a legend. You may not add or remove the legend once you have created the custom plot, although you may move its points around. We recommend you use the quicker custom plots for your own perusal of data and the slower Excel legend plots when you are preparing something to publish. You may manipulate these graphs using XYE Menu’s Chart menu bar extensions, but they are not as intuitive or as clean as using Excel’s "patterns” command.
Building Cluster Plots
Like error plots, cluster plots are built in three steps: 1) Select your Label, Dose, Response, Error and (optionally) Keys 2) Select the command “Create Cluster Plot” from XYE Menu and 3) Choose a dataspace for the macro to use as workspace. A demonstration of this is available using the “Show Me a Cluster Plot” button in the dialog box presented by selecting "XYE Menu Help”. You may optionally perform a fourth step, that of controlling how your data will look, using the "Interactive Cluster Plot” menu item. In this case a dialog box will come up for every cluster you plot, asking you what error lines and error ticks you would like for the selected data cells. Technically, steps 1 or 3 are also optional. If you skip step 1 and start the plot without enough data selected, you pop into interactive mode where you may indicate the location of your data cells. If you don’t wish to view the dataspace, you may skip step three by selecting the concealed dataspace option in the XYE Menu Defaults.
As described above in the section of XY Error Plots, the plot will be placed in back of the current windows if it is in a new chart window or embedded near your data if you are using embedded plots. Note that it is not possible to add or remove clusters once a cluster plot has been created. All the clusters are technically part of one data series.
Selecting Data For Cluster Plots
Unlike XY error plots, you must select the cells containing labels when you select data for cluster plots. With cluster plots, the label is used to determine where one cluster ends and another begins. There are other ways to do so, you may select each cluster’s data as a noncontiguous rows of data or place a blank line between each cluster (you must still select the empty cluster label column). But for the quickest entry of data it is easiest to type it in as a block and select it as a block. Remember, you may always run interactively if the different portions of the data (Label, Name, Response, Error and Keys) are at random positions in the worksheet.
Data is entered as regions that are four or five columns wide. The optional fifth column corresponds to keys that may be used to note the significance of individual bars or to identify bars of particular interest. While Excel calls a horizontal rectangle on a chart a “bar” and vertical rectangles “columns”, both are referred to as bars and "columns" is used to refer to columns in the worksheet when describing cluster plot creation (an errorbar will always be referred to in a single word). The first column contains the label for the cluster, for instance the name of the compound used in an experiment. This label is entered only once per cluster; if there are ten bars in a cluster the label appears once in the first column of the first row; the next nine rows will have data in the remaining columns but will be blank in the first column. The demonstration available using the "Show Me a Cluster Plot” button in the dialog box presented by selecting “XYE Menu Help” menu item is worth a thousand words. The second column contains the name for a bar, typically the dose of the compound used for the experiment. The third column is a result of the experiment, typically the measured response. The fourth column is the error, which is used to calculate a +/- error. The error column is not optional but may be left blank. The optional fifth column contains any text to be associated with a specific data point and placed on the chart. These "keys" slow the macro by about a second for each text mark placed in the plot.
If input is delimited by blank lines or noncontiguous regions of data and a label is blank, the label will be filled with a generic name (e.g. “Cluster 1”) if the “Generate names for unlabeled curves/clusters” option is set. If the “Link names in legend to worksheet” option is set cell references will be used to pass the name to the chart rather than text names. Both options are set using the XYE Menu Defaults (described at the top of this document).
Setting the Color and Pattern of the Bars
Note that any colors and patterns applied to the cell containing the name of the cluster will be used in the resulting plot as the patterns and colors for the bars of that cluster if the "Color Coded Labels" option is set in the Cluster Defaults.
Selecting the Dataspace
This is done exactly the same way as described above for XY error plots. Because all the clusters are technically part of one data series, you only need to select one dataspace for all the clusters.
Cluster Dialog Box Items:
If you start the plot without enough data selected or use the interactive mode (see "Expert Mode" of "XYE Menu Defaults"), a dialog box containing the following items will appear.
Enter, Then Plot
Enter Series
Plot
Cancel
These buttons control the flow of the program. “Enter, Then Plot” means you wish to enter the displayed data series then see the plot for all the data series you have entered. “Enter Series” means you wish to enter the displayed data series and proceed to the next data series. “Plot” means you have no more data to enter and wish to see the plot for all the data series you have entered (any information in the current dialog is ignored). “Cancel” means you have decided not to proceed with the plot and wish to return to the normal Excel environment.
Direction of Errorbars
Error Ticks
What do the errorbars look like? There are two groups of option buttons that describe the appearance of the errorbar: Direction and Error Ticks. They are pretty much self explanatory. Because the points on a cluster plot can not overlap like the lines of an XY error plot, the dialog box has been made more economical by leaving out the error line option buttons present in the XY error dialog. If you wish to set error line styles you may do so by selecting an individual line on the plot by holding down the control key while clicking on the line. Then double click the line to change it.
Cells
Keys Used
The range of each column of cells used for the current curve are in the lower right hand corner of the dialog box. The data columns may be changed directly. Just hit the tab key until the value you wish to replace is selected, move the dialog box by dragging its title, find your data, and then click and drag on the range you wish to select. You may also type the references if you prefer. The checkbox next to keys should be checked if you wish to use keys. Do this before entering other data, the dialog box will attempt to guess where your key data is located and this may change the ranges entered in other cells.
Label
There are two styles of labels, linked and unlinked. Select your preference using the “Defaults for XYE Menu” item. If you have linked labels the dialog box will ask for a cell. Click on the appropriate cell or type in a reference. If you have unlinked labels the dialog box will ask for text. You may still type in the label of a cell, but it will appear as a text name on your plot.
Silent
The “Silent” check box is perhaps the most useful box. It prevents the dialog box from returning. If you check this box all remaining preselected data will be entered automatically using the selected attributes. If color or fill are “rolling” as described below they will differ from cluster to cluster, the “roll” attribute supersedes any color attributes.
Color
Fill
Roll
These items only appear if the "Color Coded Labels" option is set in the Cluster Defaults dialog (described below). If no color and pattern has been applied to the name cell of the data, the color and pattern used to fill the bars of the cluster are those selected in the color and fill box. Note the checkbox saying “Roll” next to the color and fill boxes. If the checkbox is checked, the selection will change automatically each time the dialog box pops up, rolling down the list. This is useful in conjunction with the “Silent” checkbox described above.
Cluster Defaults
The items in the cluster defaults dialog are similar to those of interactive cluster dialog box, with the exception of
1) “Blanks Between Clusters”, which allows you set the number of blank spaces between each cluster.
2) "Color Coded Labels", which places labels for the clusters on the plot. This option slows down the plot routine, but creates labels (Excel's legend doesn't look very pretty).
3) The “varied” options for Color and Fill, which will cause each cluster in a single plot to have a different color or fill (equivalent to check to checking the “Roll” checkbox for Color or Fill in the interactive dialog box). The color and fill portions of the dialog are not visible unless the "Color Coded Labels" checkbox is checked.
Building Column Error Plots
Like error plots, column plots are built in three steps: 1) Select your Name, Y, Error and (optionally) Keys columns 2) Select the command “Create Column Error Plot” from XYE Menu and 3) Choose a dataspace for the macro to use as workspace. A demonstration of this is available using the “Show Me A Column Plot” button in the dialog box presented by selecting “XYE Menu Help”. You may optionally perform a fourth step, that of controlling how your data will look, using the Interactive Column Error Plot” menu item. In this case a dialog box will come up for every data series you plot, asking you what error lines and error ticks you would like for the selected data cells. Technically, steps 1 or 3 are also optional. If you skip step 1 and start the plot without enough data selected, you pop into interactive mode where you may indicate the location of your data cells. You may skip step 3 if you don’t wish to view the dataspace, Excel will conceal the dataspace for you (see information on XYE Menu Defaults at the top of this document).
As described above in the section of XY Error Plots, the plot will be placed in back of the current windows if it is in a new chart window or embedded near your data if you are using embedded plots. Note that it is not possible to add or remove any data series once a Column Error plot has been created.
Selecting Data For Column Error Plots
The first step to building an error plot is to select the data. The first column may be a column containing category labels. The category labels are the names used along the plot's X axis at the base of each group of bars. You may control the presence of category labels by checking the “First Column is Category (X) Axis Labels” option box in the Column Error Defaults dialog box or the interactive dialog box. This one column of category labels applies to all data series, you only enter it once for the entire plot. The next two columns consist of Y values and Error values (second and third columns if category labels are used, first and second columns if category labels are not used). Optionally, the next column may be Keys used to mark specific bars in the plot. This can be used to identify the statistical significance of particular bars. You may control the presence of Keys by checking the “No Keys Used” option box in the Column Error Defaults dialog box or the “Keys” option box in the interactive dialog box. You may include as many subsequent Y, Error, and Keys columns as you like. Caveat: if you are running in noninteractive mode, there must be keys for all series of Y and Error data or no keys for any of the data, you must use interactive mode to avoid this all or nothing situation, although you may kludge it by using columns of blanks as keys.
The data must be in columnar form. That is, the category labels are stored in a range of cells one on top of the other, the first series of Y values one on top of the other in another column, the first series of Error values one on top of the other, then the optional keys column, the second series of Y values, etc. You may select the data as a contiguous block of cells (several columns of data next to each other) or as a noncontiguous multiple selection (by holding down the control key while dragging the mouse through additional ranges of data). When using noncontiguous ranges, each range may contain one or more columns of cells. The program breaks up multicolumn ranges into ranges of one column each. The error values are used to calculate +/- error values, you may change the high and low error values by editing the dataspace if the error values are not simple dispersion values. If a label is located in the cell directly above a Y column, this label will be used in the legend for the data, otherwise a generic label is created. You may optionally change the label and column entries in the interactive dialog box.
Selecting the Dataspace
This is done exactly the same way as described above for XY error plots. Because all the errorbars are technically part of one data series, you only need to select one dataspace for all the columns.
Column Error Dialog Box Items:
If you start the plot without enough data selected or use the interactive mode (see "Expert Mode" of "XYE Menu Defaults"), a dialog box containing the following items will appear.
Enter, Then Plot
Enter Series
Plot
Cancel
These buttons control the flow of the program. “Enter, Then Plot” means you wish to enter this data series then see the plot for all the data series you have entered. “Enter Series” means you wish to enter this data series and proceed to the next data series. “Plot” means you have no more data to enter and wish to see the plot for all the data series you have entered (any information in the current dialog is ignored). “Cancel” means you have decided not to proceed with the plot and wish to return to the normal Excel environment.
Direction of Errorbars
Error Ticks
What do the errorbars look like? There are two groups of option buttons that describe the appearance of the errorbar: Direction and Error Ticks. They are pretty much self explanatory. Because the points on a column error plot can not overlap like the lines of an XY error plot, the dialog box has been made more economical by leaving out the error line option buttons present in the XY error dialog. If you wish to set error line styles you may do so by selecting an individual line on the plot by holding down the control key while clicking on the line. Then double click the line to change it.
Cells With Keys
First Column Is Category (X) Axis Labels
These option boxes are somewhat self explanatory, if they are checked the plot will contain the additional data of category labels and/or keys. Because these options control the amount of information used in the plot, it is best to set these first before entering data into other cells. The dialog box will attempt to guess where your category and/or key data is located and this may change the ranges entered in other cells.
Cells
The range of each column of cells used for the current curve are in the lower right hand corner of the dialog box. The data columns may be changed directly. Just hit the tab key until the value you wish to replace is selected, move the dialog box by dragging its title, find your data, and then click and drag on the range you wish to select. You may also type the cell references if you prefer.
Silent
The “Silent” check box is perhaps the most useful box. It prevents the dialog box from returning. If you check this box all remaining preselected data will be entered automatically using the selected attributes. If color or fill are “rolling” as described below they will differ from series to series, the "roll" attribute supersedes any color attributes.
Color
Fill
Roll
These items only appear if the "Color Coded Labels" option is set in the Column Error Defaults dialog (described below). If no color and pattern has been applied to the name cell of the data, the color and pattern used to fill the bars of the series are those selected in the color and fill box. Note the checkbox saying “Roll” next to the color and fill boxes. If the checkbox is checked, the selection will change automatically each time the dialog box pops up, rolling down the list. This is useful in conjunction with the “Silent” checkbox described above.
Column Error Defaults
If the "Color Coded Labels" dialog box is checked, labels for the series of the data will be placed in the Plot. This fixes the problem with Excel's legend when using errorbars, but slows down the building of a plot. When Color Coded Labels are enabled the selection boxes for choosing the fill pattern and color are enabled. The “varied” options for Color and Fill will cause each column in a single plot to have a different color or fill (equivalent to check to checking the “Roll” checkbox for Color or Fill in the interactive dialog box). The other items in the column defaults dialog are similar to those of interactive column error dialog box, with the exception of “Blanks Between Categoriess”, which allows you set the number of blank spaces between each category.
Calculating Average/Error
If you do not already have error values for your data, it is possible to calculate the average and normal standard error of the mean for your data. Just select the block of data and then calculate noninteractively using the "Calculate Average/Error" command or interactively using "Interactive Calculate". The options are the same for noninteractive and interactive calculations. You set the options for noninteractive calculations using the "Average/Error Defaults" command.
Standard Error is simply the z-value of the error level times the standard deviation divided by the square root of the number of data points. The standard error of the mean is the error of the expected value of the true mean as represented by the mean of the set of real life data.
There are no multiple series or multiple areas for calculating average/error, you are expected to select the block of raw data before running either interactive or noninteractive modes.
The Average/Error Dialog & Default Settings
Results
Usually you will require both average and error in the order average, error. If you wish to have just the error, just the average, or the error and average in reverse order select the appropriate radio button in the results box.
Calculate For
Usually you will want the results for the horizontal rows (which appears in vertical columns), but if you wish to have the results for columns or both columns and rows, select the appropriate radio button in the "Calculate For" box.
Population
This is an important, salient point. What do you wish to calculate the standard error for? If you have the entire population (e.g. length of all American automobiles), you may select the "Complete" radio button. If you do not have the complete population (e.g. experimental datapoints reflecting an unknown absolute experimental value), select the "Sampled" radio button. Remember, it is important to know both the quantity being measured and the quantity being calculated. If you have the average IQ for a class of 37 8th grade students and wish to calculate the standard error for the class of 37 students, select "Complete". If you wish to calculate the average IQ for the entire eighth grade at the school or all the US (editor: poor sampling technique for all the US), select "Sampled".
Error is
Another important, salient point. What are you looking for? If you are looking for extremely high or low values (e.g. significant fluctuations in the infrared output of 200 stars you've observed), you want double sided values, so click the "Double Sided" radio button. If you are looking for only extremely high (e.g. seeds of corn that grew high) or extremely low (e.g. colonies whose growth was significantly inhibited by a compound added to the growth media) values, you are only interested in a single side of the values, so click the "Single Sided" radio button.
Error
95% error is usually considered inclusive of normally expected values, but this is an arbitrary value. You may select more extreme values if you wish. If you want a value not represented by the errors shown here, try a custom Z-Value.
Custom Z-Value
Use this checkbox if you have looked up the coefficient of error for a normal curve representing the error level you want (it is assumed you know what you are doing). A box will appear for you to enter your own z-value.
Place Result Cells Manually
It is assumed you want your results placed below or to the right of the block of data selected. If you wish to place the results elsewhere, check this checkbox.
Chart Commands - Changing A Plot
When working with an XY Error plot, you might double click on a curve and edit attributes such as the color or symbol. Using errorbars, unless you specified a single point (by holding down the control key while clicking), you will destroy the styling of an entire series of data in the process. The XYE Menu provides you with two types of commands: 1) Those that reset the style of the plot after you have made changes using Excel’s Chart Patterns command, usually invoked by double clicking on a chart object 2) Those that understand the altered style of the XYE plots and will implement your changes in a way appropriate to the XYE plot. Some chart commands are available only in expert mode (see information on XYE Menu Defaults at the top of this document).
Note: Reformatting the Legend for Custom Legend XY Error Plots:
The custom legend contains three points. A line is drawn from the left point to the middle point which has a symbol, then the line continues to the right point. The left and right point have no symbols, they exist to provide the “wings” that make the middle point with it’s symbol appear to have a line through it. To move the legend up and down, hold down the control key while clicking on the symbol, then drag that point to the desired height. The wings will automatically readjust themselves to the same height as the symbol. Horizontal position must be set for each of the three points individually.
Reset Error Stylings (Frozen)
If you have altered an XY error plot with an Excel type legend, this command will change it back into an XY error plot.
Reset Styling Defaults (Frozen)
This command allows you to set the attributes that will be applied to the curves when they are restyled using the “Reset Error Stylings (Frozen)” command. They are all analogous to the commands found in the XY error plot dialog box.
Interactive Reset (Frozen) - expert mode only
This command allows you to alter the errorbars without changing the XY error style of plot with an Excel type legend. You may specify the attributes of the errorbar style as you did in the errorbar creation dialog. All dialog items are exactly analogous to the same items in the XY error plot creation dialog. There is one extra feature, a button that allows you to skip over a curve without restyling the curve. If you remember the numbers of the curves in the Excel chart document (e.g. Series 3 or just S3), you may skip over curves that do not need to be reset. When you click on a curve in a chart the number of the curve appears in the reference area, just to the left of the formula bar (this is not true while a dialog box is active, however).
Paste Curve Symbols (Frozen) - expert mode only
To add custom symbols, create a graphic object you would like as a symbol (e.g. a thin oval using the oval tool from the Excel tool bar), select it, and copy it. Next make a chart the active window and run the “Paste Symbols (Frozen)” command. To remove customization, select the line and use the "Clear" item from the Edit menu.
Paste Error Symbols (Frozen) - expert mode only
This is exactly analogous to the “Paste Curve Symbols (Frozen)” command directly above, except that it uses the symbols as error ticks.
Reset Legend (Custom)
If the appearance of the legends have been altered by manipulating curves with Excel’s chart “Patterns” command, the legends may be reset to their proper appearance using this command. It simply selects the first and third points of the legend and removes any symbols, then selects the second and third points and adds the lines drawn. The order of the points in the legend is described in the section immediately above.
How It Works
XY Error with Excel legend
We must have only one series in our plot for each curve in order to use the Excel legend. XY Error works by using four pseudo points to describe each actual point. For every data point XY Error creates 1) the identical point, as a starting point, 2) the low error boundary, 3) the high error boundary, 4) the identical point, as an endpoint for drawing the error. All four points have the same X value. When the line is plotted, the line is "stitched", drawing to the actual point, then down, then up, then back to the actual point before it proceeds to the next actual point.
Custom XY Error
The goal here is to let the user customize lines and symbols in the plot by simply double clicking on them. We use five lines for each curve, one for the high error tick, one for the low error tick, one for the curve, one for the error line between the high error tick and the curve mark, and one for the line between the curve mark and the low error tick. This way the user may change the symbols and even the high and/or low nature of the errorbars.
Custom XY Error with legend
To extend the custom XY error technique to include a legend, four points are added to the beginning of the line used for the curve (always the fifth of the five). The first is the left wing, the second is the curve mark, the third is the right wing, and the fourth is an empty point to keep Excel from connecting the legend points to the rest of the data for the curve. The symbols for the first and third points are set to none, so we see a simple point with a line “through” it. This line is made up of the lines connecting the first point to the second point and the second point to the third point. The maximum value of the Y data is used as the Y coordinate for the first point and the second and third points are set equal to this. The X coordinates for the first, second, and third points are the X value of the last point in the graph plus the difference in the distance between the last and next to last point multiplied by one, two and three respectively.
Cluster Plot
This is really a variation on the high/low close overlay. We have a series for each Y column, the series for the high ticks, and then another series for the low error tick. When all the data has been read in from the user, all the error values are serially placed into two long data series. Each Y series is preceded with a number of blanks corresponding to the number of values in previous series and the blanks between clusters. Overlap (from the graph menu) is then set to 100%. Then when we create an overlay using the high and low data series, the high low values are centered.
Column Error Plot
Unlike cluster plots, native Excel can do column plots where the data series alternate in each category. Unfortunately, Excel can create only one set of droplines for errorbars. This macro places each alternating data series in a separate dataspace column (so it may be changed by double clicking the plot). All the errors are placed into one long series for the low error ticks and one long series for the high error ticks. Overlap is then set to 100% so that each data series point inhabits an entire category. Each group of categories near each other comprise a pseudo-category. The label for each pseudo-category is then placed under the int(n/2)+1 point in the pseudo-category.
The Dataspace
There are two steps, that of massaging the data selected by the user into a form which can be used as series in a chart and then producing the chart using the edit.series command and others. In between the manipulations and the charting, the data for drawing these plots has to be placed somewhere. Once used to create a chart, it can not be deleted or the chart will become gibberish; the chart is linked to the data in the dataspace. There are two styles of placing the dataspace for these curves. “Computer concealed” places the data in eight indented columns that materialize in the front of the worksheet, using Excel’s outlining abilities. A concealed dataspace is created once, if you build another plot on a worksheet with a concealed space the lower rows of the dataspace will be used. If expansion is needed, more columns may be swallowed by the concealed dataspace. The other data will be moved and embedded charts will be updated. Nonembedded charts may be updated if they are open at the time the dataspace is created or expanded. You can observe and edit the data, simply click on the “+” button above the column containing the error data.
Alternatively, the user may provide an area for the dataspace. There are two options, “Computer Checked” where the computer checks to see if there is any data that will be overwritten (minimally slower) and the normal style where it blithely overwrites any data that may happen to be there. The additional data necessary to build the plot is entered. The macros that place the data from the selected cells into the dataspace do not copy the selected values, but rather write functions that refer to the selected values. Thus, if you change an X value in the data used to build the plot you will see the plot in the chart window change to reflect the new X value.
In conclusion, enjoy. Be careful to always select your data properly; writing code to check for every possible misinput of data would have slowed the routines down even more and we’re pushing the limits of what Excel can do already. Use the demos when you are curious, if you change an item in any of your defaults go ahead and run the demo again, it will change to reflect the defaults. Feel free to use the demo data to create additional plots.
